package com.gopay.scheduler.job;

import com.gopay.common.airticket.ReceivablesStatisticDAO;
import com.gopay.common.domain.airticket.ReceivablesStatistic;
import com.gopay.common.order.dao.CommonQueryDAO;
import com.gopay.scheduler.GopayBaseJob;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;

/**
 * 收款统计任务
 * 
 * @author zhaoxubing
 *
 */
public class ReceivablesStatisticsJob extends GopayBaseJob {

	private static final int pageSize = 1000;
	private static final String yyyyMMdd = "yyyyMMdd";
	private Logger logger = LoggerFactory.getLogger(this.getClass());
	private SimpleDateFormat sdf = new SimpleDateFormat(yyyyMMdd);
    /**
     * 公共查询Dao
     */
    @Resource(name = "commonQueryDAO")
    private CommonQueryDAO commonQueryDAO;
    
    /**
     * 收款统计表
     */
    
    @Resource(name = "receivablesStatisticDAO")
    private ReceivablesStatisticDAO receivablesStatisticsDAO;
	
	@Override
	@Transactional
	public void doJobInternal() {
		logger.info("--------收款统计ReceivablesStatisticsJob  start----");
		Calendar cal = Calendar.getInstance();
		Date today = cal.getTime();
		String todayStr = sdf.format(today);
		cal.add(Calendar.DATE, -1);
		Date yesterday = cal.getTime();
		String yesterdayStr = sdf.format(yesterday);

		/*  			
			收款=成功订单的交易金额
			退款=退款订单的退款交易金额
			收款净额=收款-退款
			应付手续费=成功订单的手续费+收款退款手续费-退款订单的退还手续费
			应结金额=收款净额-应付手续费=收入-支出
			退款失败金额=退款失败订单的交易金额+转账退回的交易金额+收款失败退款手续费-退款失败订单的退还手续费-转账退回的退还手续费（退网关支付）
			实结金额=应结金额+退款失败金额=收入-支出+退款失败金额*/
		
		// 获取收款信息
		Map<String, ReceivablesStatistic> recieptResults = getRecieptResults(todayStr, yesterdayStr);
		logger.info("--------收款信息recieptResults=" + recieptResults);
		// 获取退款信息
		Map<String, ReceivablesStatistic> refundResults = getRefundResults(todayStr, yesterdayStr);
		logger.info("--------退款信息refundResults=" + refundResults);

		// 获取手续费信息
		Map<String, ReceivablesStatistic> feeResults = getFeeResults(todayStr, yesterdayStr);
		logger.info("--------手续费信息feeResults=" + feeResults);

		// 收款退款手续费信息
		Map<String, ReceivablesStatistic> receRefundFeeAmtResults = getReceRefundFeeAmtResults(todayStr, yesterdayStr);
		logger.info("--------收款退款手续费信息receRefundFeeAmtResults=" + receRefundFeeAmtResults);

		// 获取退款退还的手续费信息
		Map<String, ReceivablesStatistic> refundFeeResults = getRefundFeeResults(todayStr, yesterdayStr);
		logger.info("--------退款退还的手续费信息refundFeeResults=" + refundFeeResults);
		
		// 获取退款失败的退还的手续费信息
		Map<String, ReceivablesStatistic> refundFailFeeResults = getRefundFailFeeResults(todayStr, yesterdayStr);
		logger.info("--------退款失败的退还的手续费信息refundFailFeeResults=" + refundFailFeeResults);

		// 转账退回的退还手续费（退网关支付）计入退款失败订单的退还手续费
		Map<String, ReceivablesStatistic> transferBackFeeResults = getTransferBackFeeResults(todayStr, yesterdayStr);
		logger.info("--------转账退回的退还手续费（退网关支付）信息transferBackFeeResults=" + transferBackFeeResults);

		// 获取退款失败信息
		Map<String, ReceivablesStatistic> refundFailResults = getRefundFailResults(todayStr, yesterdayStr);
		logger.info("--------退款失败信息refundFailResults=" + refundFailResults);

		// 转账退回的交易金额
		Map<String, ReceivablesStatistic> transferBackResults = getTransferBackResults(todayStr, yesterdayStr);
		logger.info("--------转账退回的交易信息transferBackResults=" + transferBackResults);

		// 收款退款失败手续费信息
		Map<String, ReceivablesStatistic> receRefundFailFeeAmtResults = getReceRefundFailFeeAmtResults(todayStr, yesterdayStr);
		logger.info("--------收款退款失败手续费信息receRefundFailFeeAmtResults=" + receRefundFailFeeAmtResults);
		
		this.mergeRefundResultsMap(recieptResults,refundResults);
		this.mergeRefundFeeResultsMap(recieptResults,refundFeeResults);
		this.mergeRefundFailFeeResultsMap(recieptResults,refundFailFeeResults);
		this.mergeTransferBackFeeResultsMap(recieptResults,transferBackFeeResults);
		this.mergeFeeResultsMap(recieptResults,feeResults);
		this.mergeRefundFailResultsMap(recieptResults,refundFailResults);
		this.mergeTransferBackResultsMap(recieptResults,transferBackResults);
		this.mergeReceRefundFeeAmtResultsMap(recieptResults,receRefundFeeAmtResults);
		this.mergeReceRefundFailFeeAmtResultsMap(recieptResults,receRefundFailFeeAmtResults);

		logger.info("--------合并数据recieptResults=" + recieptResults);
		Iterator<Entry<String, ReceivablesStatistic>> iterator = recieptResults.entrySet().iterator();
		BigDecimal zero = new BigDecimal(0);
		while(iterator.hasNext()){
			try{
				Entry<String, ReceivablesStatistic> entry = iterator.next();
				//计算
				ReceivablesStatistic value = entry.getValue();
				//收款净额=收款-退款
				BigDecimal recieptAmt = null==value.getRecieptAmt() ? new BigDecimal(0):value.getRecieptAmt();
				BigDecimal refundAmt = null==value.getRefundAmt() ? new BigDecimal(0):value.getRefundAmt();
				BigDecimal netreceivablesAmt = recieptAmt.subtract(refundAmt);
				value.setNetreceivablesAmt(netreceivablesAmt);
				
				//应付手续费=成功订单的手续费+收款退款手续费信息-退款成功订单的退还手续费
				BigDecimal fee = null==value.getFee() ? new BigDecimal(0) : value.getFee();
				BigDecimal receRefundFeeAmt = null == value.getReceRefundFeeAmt() ? new BigDecimal(0) : value.getReceRefundFeeAmt();
				BigDecimal refundFeeAmt = null==value.getRefundFeeAmt() ? new BigDecimal(0):value.getRefundFeeAmt();

				BigDecimal shouldFeeAmt = fee.add(receRefundFeeAmt).subtract(refundFeeAmt);
				value.setShouldFeeAmt(shouldFeeAmt);
				
				//应结金额=收款净额-应付手续费
				value.setShouldSettlementAmt(value.getNetreceivablesAmt().subtract(shouldFeeAmt));

				//退款失败金额=退款失败订单的交易金额+转账退回的交易金额+收款退款失败手续费信息-退款失败订单的退还手续费-转账退回的退还手续费（退网关支付）
				BigDecimal refundFailAmt = null == value.getRefundFailAmt() ? new BigDecimal(0):value.getRefundFailAmt();
				BigDecimal refundFailFeeAmt = null == value.getRefundFailFeeAmt() ? new BigDecimal(0):value.getRefundFailFeeAmt();
				BigDecimal transferBackAmt = null == value.getTransferBackAmt() ? new BigDecimal(0):value.getTransferBackAmt();
				BigDecimal transferBackFeeAmt = null == value.getTransferBackFeeAmt() ? new BigDecimal(0):value.getTransferBackFeeAmt();
				BigDecimal receRefundFailFeeAmt = null == value.getReceRefundFailFeeAmt() ? new BigDecimal(0):value.getReceRefundFailFeeAmt();

				BigDecimal shouldRefundFailAmt = refundFailAmt.add(transferBackAmt).add(receRefundFailFeeAmt).subtract(refundFailFeeAmt).subtract(transferBackFeeAmt);
				value.setRefundFailAmt(shouldRefundFailAmt);
				
				//实结金额=应结金额+退款失败金额
				value.setActualSettlementAmt(value.getShouldSettlementAmt().add(shouldRefundFailAmt));
				value.setCountTime(new Timestamp(System.currentTimeMillis()));
				
				//将笔数为空的置为0
				String recieptCount = null == value.getRecieptCount()?"0":value.getRecieptCount();
				value.setRecieptCount(recieptCount);
				
				String refundCount = null == value.getRefundCount()?"0":value.getRefundCount();
				value.setRefundCount(refundCount);

				//退款失败笔数 + 转账退回笔数
				String refundFailCount = null == value.getRefundFailCount()?"0":value.getRefundFailCount();
				String transferBackCount = null == value.getTransferBackCount()?"0":value.getTransferBackCount();
				String failCount = String.valueOf(Integer.parseInt(refundFailCount) + Integer.parseInt(transferBackCount));
				value.setRefundFailCount(failCount);

				receivablesStatisticsDAO.save(value);
			}catch(Exception e){
				logger.error("保存统计信息出错",e);
			}	
		}
		logger.info("--------收款统计ReceivablesStatisticsJob  end----");
	}


	private void mergeReceRefundFeeAmtResultsMap(
			Map<String, ReceivablesStatistic> recieptResults,
			Map<String, ReceivablesStatistic> transferBackResults) {
		if(CollectionUtils.isEmpty(recieptResults))
			recieptResults = new HashMap<String, ReceivablesStatistic>();

		if(CollectionUtils.isEmpty(transferBackResults))
			return;

		Iterator<Entry<String, ReceivablesStatistic>> iterator = transferBackResults.entrySet().iterator();
		while(iterator.hasNext()){
			Entry<String, ReceivablesStatistic> entry = iterator.next();
			String key = entry.getKey();
			ReceivablesStatistic v = entry.getValue();
			ReceivablesStatistic sourceValue = recieptResults.get(key);
			if(null != sourceValue){
				sourceValue.setCountTime(v.getCountTime());
				sourceValue.setReceRefundFeeAmt(v.getReceRefundFeeAmt());
			}else{
				recieptResults.put(key, v);
			}
		}
	}

	private void mergeReceRefundFailFeeAmtResultsMap(
			Map<String, ReceivablesStatistic> recieptResults,
			Map<String, ReceivablesStatistic> transferBackResults) {
		if(CollectionUtils.isEmpty(recieptResults))
			recieptResults = new HashMap<String, ReceivablesStatistic>();

		if(CollectionUtils.isEmpty(transferBackResults))
			return;

		Iterator<Entry<String, ReceivablesStatistic>> iterator = transferBackResults.entrySet().iterator();
		while(iterator.hasNext()){
			Entry<String, ReceivablesStatistic> entry = iterator.next();
			String key = entry.getKey();
			ReceivablesStatistic v = entry.getValue();
			ReceivablesStatistic sourceValue = recieptResults.get(key);
			if(null != sourceValue){
				sourceValue.setCountTime(v.getCountTime());
				sourceValue.setReceRefundFailFeeAmt(v.getReceRefundFailFeeAmt());
			}else{
				recieptResults.put(key, v);
			}
		}
	}

	private void mergeTransferBackResultsMap(
			Map<String, ReceivablesStatistic> recieptResults,
			Map<String, ReceivablesStatistic> transferBackResults) {
		if(CollectionUtils.isEmpty(recieptResults))
			recieptResults = new HashMap<String, ReceivablesStatistic>();
		
		if(CollectionUtils.isEmpty(transferBackResults))
			return;
		
		Iterator<Entry<String, ReceivablesStatistic>> iterator = transferBackResults.entrySet().iterator();
		while(iterator.hasNext()){
			Entry<String, ReceivablesStatistic> entry = iterator.next();
			String key = entry.getKey();
			ReceivablesStatistic v = entry.getValue();
			ReceivablesStatistic sourceValue = recieptResults.get(key);
			//logger.info("sourceValue=" + sourceValue + "    v=" + v);
			if(null != sourceValue){
				sourceValue.setCountTime(v.getCountTime());
				sourceValue.setTransferBackAmt(v.getTransferBackAmt());
				sourceValue.setTransferBackCount(v.getTransferBackCount());
			}else{
				recieptResults.put(key, v);
			}
		}
		
	}

	private void mergeRefundFailResultsMap(
			Map<String, ReceivablesStatistic> recieptResults,
			Map<String, ReceivablesStatistic> refundFailResults) {
		if(CollectionUtils.isEmpty(recieptResults))
			recieptResults = new HashMap<String, ReceivablesStatistic>();

		if(CollectionUtils.isEmpty(refundFailResults))
			return;

		Iterator<Entry<String, ReceivablesStatistic>> iterator = refundFailResults.entrySet().iterator();
		while(iterator.hasNext()){
			Entry<String, ReceivablesStatistic> entry = iterator.next();
			String key = entry.getKey();
			ReceivablesStatistic v = entry.getValue();
			ReceivablesStatistic sourceValue = recieptResults.get(key);
			//logger.info("sourceValue=" + sourceValue + "    v=" + v);
			if(null != sourceValue){
				sourceValue.setCountTime(v.getCountTime());
				sourceValue.setRefundFailAmt(v.getRefundFailAmt());
				sourceValue.setRefundFailCount(v.getRefundFailCount());
			}else{
				recieptResults.put(key, v);
			}
		}

	}

	private void mergeFeeResultsMap(
			Map<String, ReceivablesStatistic> recieptResults,
			Map<String, ReceivablesStatistic> feeResults) {
		if(CollectionUtils.isEmpty(recieptResults))
			recieptResults = new HashMap<String, ReceivablesStatistic>();
		
		if(CollectionUtils.isEmpty(feeResults))
			return;
		
		Iterator<Entry<String, ReceivablesStatistic>> iterator = feeResults.entrySet().iterator();
		while(iterator.hasNext()){
			Entry<String, ReceivablesStatistic> entry = iterator.next();
			String key = entry.getKey();
			ReceivablesStatistic v = entry.getValue();
			ReceivablesStatistic sourceValue = recieptResults.get(key);
			//logger.info("sourceValue=" + sourceValue + "    v=" + v);
			if(null != sourceValue){
				sourceValue.setCountTime(v.getCountTime());
				sourceValue.setFee(v.getFee());
			}else{
				recieptResults.put(key, v);
			}
		}
		
	}


	private void mergeRefundFailFeeResultsMap(
			Map<String, ReceivablesStatistic> recieptResults,
			Map<String, ReceivablesStatistic> refundFailFeeResults) {
		if(CollectionUtils.isEmpty(recieptResults))
			recieptResults = new HashMap<String, ReceivablesStatistic>();
		
		if(CollectionUtils.isEmpty(refundFailFeeResults))
			return;
		
		Iterator<Entry<String, ReceivablesStatistic>> iterator = refundFailFeeResults.entrySet().iterator();
		while(iterator.hasNext()){
			Entry<String, ReceivablesStatistic> entry = iterator.next();
			String key = entry.getKey();
			ReceivablesStatistic v = entry.getValue();
			ReceivablesStatistic sourceValue = recieptResults.get(key);
			//logger.info("sourceValue=" + sourceValue + "    v=" + v);
			if(null != sourceValue){
				sourceValue.setCountTime(v.getCountTime());
				sourceValue.setRefundFailFeeAmt(v.getRefundFailFeeAmt());
			}else{
				recieptResults.put(key, v);
			}
		}
		
	}

	private void mergeTransferBackFeeResultsMap(
			Map<String, ReceivablesStatistic> recieptResults,
			Map<String, ReceivablesStatistic> transferBackFeeResults) {
		if(CollectionUtils.isEmpty(recieptResults))
			recieptResults = new HashMap<String, ReceivablesStatistic>();

		if(CollectionUtils.isEmpty(transferBackFeeResults))
			return;

		Iterator<Entry<String, ReceivablesStatistic>> iterator = transferBackFeeResults.entrySet().iterator();
		while(iterator.hasNext()){
			Entry<String, ReceivablesStatistic> entry = iterator.next();
			String key = entry.getKey();
			ReceivablesStatistic v = entry.getValue();
			ReceivablesStatistic sourceValue = recieptResults.get(key);
			//logger.info("sourceValue=" + sourceValue + "    v=" + v);
			if(null != sourceValue){
				sourceValue.setCountTime(v.getCountTime());
				sourceValue.setTransferBackFeeAmt(v.getTransferBackFeeAmt());
			}else{
				recieptResults.put(key, v);
			}
		}

	}


	private void mergeRefundFeeResultsMap(
			Map<String, ReceivablesStatistic> recieptResults,
			Map<String, ReceivablesStatistic> refundFeeResults) {
		if(CollectionUtils.isEmpty(recieptResults))
			recieptResults = new HashMap<String, ReceivablesStatistic>();
		
		if(CollectionUtils.isEmpty(refundFeeResults))
			return;
		
		Iterator<Entry<String, ReceivablesStatistic>> iterator = refundFeeResults.entrySet().iterator();
		while(iterator.hasNext()){
			Entry<String, ReceivablesStatistic> entry = iterator.next();
			String key = entry.getKey();
			ReceivablesStatistic v = entry.getValue();
			ReceivablesStatistic sourceValue = recieptResults.get(key);
			//logger.info("sourceValue=" + sourceValue + "    v=" + v);
			if(null != sourceValue){
				sourceValue.setCountTime(v.getCountTime());
				sourceValue.setRefundFeeAmt(v.getRefundFeeAmt());
			}else{
				recieptResults.put(key, v);
			}
		}
		
	}


	private void mergeRefundResultsMap(Map<String, ReceivablesStatistic> recieptResults,Map<String, ReceivablesStatistic> refundResults) {
		if(CollectionUtils.isEmpty(recieptResults))
			recieptResults = new HashMap<String, ReceivablesStatistic>();
		
		if(CollectionUtils.isEmpty(refundResults))
			return;
		
		Iterator<Entry<String, ReceivablesStatistic>> iterator = refundResults.entrySet().iterator();
		while(iterator.hasNext()){
			Entry<String, ReceivablesStatistic> entry = iterator.next();
			String key = entry.getKey();
			ReceivablesStatistic v = entry.getValue();
			ReceivablesStatistic sourceValue = recieptResults.get(key);
			//logger.info("sourceValue=" + sourceValue + "    v=" + v);
			if(null != sourceValue){
				sourceValue.setCountTime(v.getCountTime());
				sourceValue.setRefundAmt(v.getRefundAmt());
				sourceValue.setRefundCount(v.getRefundCount());
			}else{
				recieptResults.put(key, v);
			}
		}
	}


	private Map<String,ReceivablesStatistic> getRefundFailResults(String todayStr,
			String yesterdayStr) {
		// 之所以分开这么查询，是因为组合起来轮询查太慢了
		String sql = "  select NVL(sum(c.mer_txn_amt),0),c.pay_acct ,NVL(count(c.gopay_order_id),0)";
		sql += " from cps_gen_main_order c,cust_corp_info cc  ";
		sql += "  where c.gopay_int_txn_cd in( '01200','00701','02302','01203','01204') ";
		sql += "  and c.txn_sta_cd in ('20001','20002','20003' ) "; //,'90000','90001','90101','90102','90201','90301' 翻译为进行中 不与失败错误码一起
		sql += "  and c.pay_cust_id = cc.cust_id  ";
		sql += "  and c.tran_finish_time >= to_date('"+ yesterdayStr +"', 'yyyyMMdd')  ";
		sql += "  and c.tran_finish_time < to_date('"+ todayStr +"', 'yyyyMMdd')  ";
		sql += "  group by c.pay_acct ";

		List<Object[]> recieptResults = commonQueryDAO.findBySql(sql, 0,
				pageSize);
		if (CollectionUtils.isEmpty(recieptResults)) {
			return null;
		}
		
		Map<String,ReceivablesStatistic> map = new HashMap<String, ReceivablesStatistic>();
		for (Object[] objects : recieptResults) {
			ReceivablesStatistic r = new ReceivablesStatistic();
			r.setRefundFailAmt((BigDecimal) objects[0]);
			r.setAcctNo((String) objects[1]);
			
			String refundFailCount = null==objects[2]?"0":String.valueOf(objects[2]);
			r.setRefundFailCount(refundFailCount);
			map.put(r.getAcctNo(), r);
		}

		return map;
	}
	private Map<String,ReceivablesStatistic> getRefundFailFeeResults(String todayStr,
			String yesterdayStr) {
		// 之所以分开这么查询，是因为组合起来轮询查太慢了
		String sql = "  select NVL(sum(NVL(cg.refund_fee_amt,cp.total_refund_fee_amt)),0) refund_fail_fee_amt,c.pay_acct ,NVL(count(c.gopay_order_id),0)";
		sql += "  from cps_gen_main_order c ";
		sql += "  left join cust_corp_info cc on c.pay_cust_id = cc.cust_id ";
		sql += "  left join cps_gen_assist_order cg on c.gopay_order_id=cg.gopay_order_id    ";
		sql += "  left join cps_gen_pay_order cp on c.gopay_order_id=cp.gopay_order_id     ";
		sql += "  where ((c.gopay_int_txn_cd in( '01200','02302','01203') and c.Order_Fee_Payer = '01')";
		sql += "  or( c.gopay_int_txn_cd in( '00701') and c.Order_Fee_Payer = '00'))";
		sql += "  and c.txn_sta_cd in ('20001','20002','20003' ) ";//,'90000','90001','90101','90102','90201','90301' 90000+ 翻译为进行中 生产bug 8期
//		sql += "  and c.pay_cust_id = cc.cust_id  ";
//		sql += "  and c.gopay_order_id=cg.gopay_order_id  ";
		sql += "  and c.tran_finish_time >= to_date('" + yesterdayStr
				+ "', 'yyyyMMdd')  ";
		sql += "  and c.tran_finish_time < to_date('" + todayStr
				+ "', 'yyyyMMdd')  ";
		sql += "  group by c.pay_acct ";
		List<Object[]> recieptResults = commonQueryDAO.findBySql(sql, 0,
				pageSize);
		if (CollectionUtils.isEmpty(recieptResults)) {
			return null;
		}

		Map<String, ReceivablesStatistic> map = new HashMap<String, ReceivablesStatistic>();
		for (Object[] objects : recieptResults) {
			ReceivablesStatistic r = new ReceivablesStatistic();
			r.setRefundFailFeeAmt((BigDecimal) objects[0]);
			r.setAcctNo((String) objects[1]);
			map.put(r.getAcctNo(), r);
		}

		return map;
	}

	private Map<String,ReceivablesStatistic> getTransferBackFeeResults(String todayStr,
																	 String yesterdayStr) {
		String sql = "  select NVL(sum(cg.refund_fee_amt),0) refund_fee_amt,c.main_recv_acct,NVL(count(c.gopay_order_id),0)";
		sql += "  from cps_gen_main_order c ";
		sql += "  left join cust_corp_info cc on c.pay_cust_id = cc.cust_id ";
		sql += "  left join cps_gen_assist_order cg on c.gopay_order_id=cg.gopay_order_id    ";
		sql += "  where c.gopay_int_txn_cd in( '01204') ";
		sql += "  and c.txn_sta_cd in ('20000') ";
		sql += "  and c.tran_finish_time >= to_date('" + yesterdayStr
				+ "', 'yyyyMMdd')  ";
		sql += "  and c.tran_finish_time < to_date('" + todayStr
				+ "', 'yyyyMMdd')  ";
		//sql += "  and c.Order_Fee_Payer = '01' ";
		//sql += "  group by c.pay_acct ";
		sql += "  group by c.main_recv_acct ";
		List<Object[]> recieptResults = commonQueryDAO.findBySql(sql, 0,
				pageSize);
		if (CollectionUtils.isEmpty(recieptResults)) {
			return null;
		}

		Map<String, ReceivablesStatistic> map = new HashMap<String, ReceivablesStatistic>();
		for (Object[] objects : recieptResults) {
			ReceivablesStatistic r = new ReceivablesStatistic();
			r.setTransferBackFeeAmt((BigDecimal) objects[0]);
			r.setAcctNo((String) objects[1]);
			map.put(r.getAcctNo(), r);
		}

		return map;
	}

	private Map<String,ReceivablesStatistic> getFeeResults(String todayStr,
			String yesterdayStr) {
		// 之所以分开这么查询，是因为组合起来轮询查太慢了
		String sql = " select NVL(sum(c.TOTAL_ORDER_FEE_AMT),0),c.main_recv_acct  ,NVL(count(c.gopay_order_id),0)";
		sql += " from cps_gen_main_order c,cust_corp_info cc  ";
		sql += " where c.gopay_int_txn_cd in ('00100', '00401', '00900', '01400','02301', '01813') ";
		sql += " and c.txn_sta_cd = '20000' ";
		sql += " and c.main_recv_cust_id = cc.cust_id ";
		sql += " and c.Order_Fee_Payer = '02' ";
		sql += " and c.gopay_txn_tm >= to_date('"+ yesterdayStr +"', 'yyyyMMdd') ";
		sql += " and c.gopay_txn_tm < to_date('"+ todayStr +"', 'yyyyMMdd') ";
		sql += " group by c.main_recv_acct ";

		List<Object[]> recieptResults = commonQueryDAO.findBySql(sql, 0,
				pageSize);
		if (CollectionUtils.isEmpty(recieptResults)) {
			return null;
		}
		Map<String,ReceivablesStatistic> map = new HashMap<String, ReceivablesStatistic>();
		for (Object[] objects : recieptResults) {
			ReceivablesStatistic r = new ReceivablesStatistic();
			r.setFee((BigDecimal) objects[0]);
			r.setAcctNo((String) objects[1]);
			map.put(r.getAcctNo(), r);
		}

		return map;
	}

	private Map<String,ReceivablesStatistic> getReceRefundFeeAmtResults(String todayStr,
														   String yesterdayStr) {
		String sql = " select NVL(sum(c.TOTAL_ORDER_FEE_AMT),0),c.pay_acct,NVL(count(c.gopay_order_id),0)";
		sql += " from cps_gen_main_order c,cust_corp_info cc  ";
		sql += " where c.gopay_int_txn_cd in ('01203') ";
		sql += " and c.pay_cust_id = cc.cust_id  ";
		sql += " and c.Order_Fee_Payer = '01' ";
		sql += " and c.gopay_txn_tm >= to_date('"+ yesterdayStr +"', 'yyyyMMdd') ";
		sql += " and c.gopay_txn_tm < to_date('"+ todayStr +"', 'yyyyMMdd') ";
		sql += " group by c.pay_acct ";

		List<Object[]> recieptResults = commonQueryDAO.findBySql(sql, 0,
				pageSize);
		if (CollectionUtils.isEmpty(recieptResults)) {
			return null;
		}
		Map<String,ReceivablesStatistic> map = new HashMap<String, ReceivablesStatistic>();
		for (Object[] objects : recieptResults) {
			ReceivablesStatistic r = new ReceivablesStatistic();
			r.setReceRefundFeeAmt((BigDecimal) objects[0]);
			r.setAcctNo((String) objects[1]);
			map.put(r.getAcctNo(), r);
		}

		return map;
	}

	private Map<String,ReceivablesStatistic> getReceRefundFailFeeAmtResults(String todayStr,
																		String yesterdayStr) {
		String sql = " select NVL(sum(c.TOTAL_ORDER_FEE_AMT),0),c.pay_acct,NVL(count(c.gopay_order_id),0)";
		sql += " from cps_gen_main_order c,cust_corp_info cc  ";
		sql += " where c.gopay_int_txn_cd in ('01203') ";
		sql += " and c.txn_sta_cd in ('20001','20002','20003' ) ";
		sql += " and c.pay_cust_id = cc.cust_id  ";
		sql += " and c.Order_Fee_Payer = '01' ";
		sql += " and c.tran_finish_time >= to_date('"+ yesterdayStr +"', 'yyyyMMdd') ";
		sql += " and c.tran_finish_time < to_date('"+ todayStr +"', 'yyyyMMdd') ";
		sql += " group by c.pay_acct ";

		List<Object[]> recieptResults = commonQueryDAO.findBySql(sql, 0,
				pageSize);
		if (CollectionUtils.isEmpty(recieptResults)) {
			return null;
		}
		Map<String,ReceivablesStatistic> map = new HashMap<String, ReceivablesStatistic>();
		for (Object[] objects : recieptResults) {
			ReceivablesStatistic r = new ReceivablesStatistic();
			r.setReceRefundFeeAmt((BigDecimal) objects[0]);
			r.setAcctNo((String) objects[1]);
			map.put(r.getAcctNo(), r);
		}

		return map;
	}

	private Map<String,ReceivablesStatistic> getRefundResults(String todayStr,
			String yesterdayStr) {
		// 之所以分开这么查询，是因为组合起来轮询查太慢了
		String sql = " select NVL(sum(c.mer_txn_amt),0) refund_amt,c.pay_acct  ,NVL(count(c.gopay_order_id),0)";
		sql += " from cps_gen_main_order c,cust_corp_info cc  ";
		sql += " where c.gopay_int_txn_cd in( '01200','00701','02302','01203') ";
		// sql += "  and c.txn_sta_cd = '20000' ";包括所有的退款订单
		sql += "  and c.pay_cust_id = cc.cust_id  ";
		sql += "  and c.gopay_txn_tm >= to_date('"+ yesterdayStr +"', 'yyyyMMdd')  ";
		sql += "  and c.gopay_txn_tm < to_date('"+ todayStr +"', 'yyyyMMdd')  ";
		sql += "  group by c.pay_acct ";
		
		List<Object[]> recieptResults = commonQueryDAO.findBySql(sql, 0,
				pageSize);
		if (CollectionUtils.isEmpty(recieptResults)) {
			return null;
		}
		Map<String,ReceivablesStatistic> map = new HashMap<String, ReceivablesStatistic>();
		for (Object[] objects : recieptResults) {
			ReceivablesStatistic r = new ReceivablesStatistic();
			r.setRefundAmt((BigDecimal) objects[0]);
			r.setAcctNo((String) objects[1]);
			String refundCount = null== objects[2]?"0":String.valueOf( objects[2]);
			r.setRefundCount(refundCount);
			map.put(r.getAcctNo(), r);
		}

		return map;
	}

	private Map<String,ReceivablesStatistic> getRefundFeeResults(String todayStr,
			String yesterdayStr) {// 之所以分开这么查询，是因为组合起来轮询查太慢了
		String sql = " select NVL(sum(NVL(cg.refund_fee_amt,cp.total_refund_fee_amt)),0) refund_fee_amt,c.pay_acct,NVL(count(c.gopay_order_id),0)";
		sql += "  from cps_gen_main_order c ";
		sql += "  left join cust_corp_info cc on c.pay_cust_id = cc.cust_id ";
		sql += "  left join cps_gen_assist_order cg on c.gopay_order_id=cg.gopay_order_id    ";
		sql += "  left join cps_gen_pay_order cp on c.gopay_order_id=cp.gopay_order_id     ";
		sql += "  where ((c.gopay_int_txn_cd in( '01200','02302','01203') and c.Order_Fee_Payer = '01' )";
		sql += "  or (c.gopay_int_txn_cd in('00701') and c.Order_Fee_Payer = '00' ))";
		sql += " and c.gopay_txn_tm >= to_date('" + yesterdayStr
				+ "', 'yyyyMMdd') ";
		sql += " and c.gopay_txn_tm < to_date('" + todayStr + "', 'yyyyMMdd') ";
		sql += " group by c.pay_acct ";

		List<Object[]> recieptResults = commonQueryDAO.findBySql(sql, 0,
				pageSize);
		if (CollectionUtils.isEmpty(recieptResults)) {
			return null;
		}
		Map<String, ReceivablesStatistic> map = new HashMap<String, ReceivablesStatistic>();
		for (Object[] objects : recieptResults) {
			ReceivablesStatistic r = new ReceivablesStatistic();
			r.setRefundFeeAmt((BigDecimal) objects[0]);
			r.setAcctNo((String) objects[1]);
			map.put(r.getAcctNo(), r);
		}
		
		return map;
	}
	
	private Map<String,ReceivablesStatistic> getRecieptResults(String todayStr, String yesterdayStr) {
		// 之所以分开这么查询，是因为组合起来轮询查太慢了
		String sql = " select NVL(sum(c.total_order_amt),0) total_order_amt ,c.main_recv_acct  ,NVL(count(c.gopay_order_id),0)";
		sql += " from cps_gen_main_order c,cust_corp_info cc  ";
//		sql += " where c.gopay_int_txn_cd in ('00100', '00401', '00900', '01400') "; 
		sql += " where c.gopay_int_txn_cd in ('00100', '00401', '01601', '01602','02301', '01813') ";
		sql += " and c.main_recv_cust_id = cc.cust_id ";
		sql += " and c.txn_sta_cd = '20000' ";
		sql += " and c.gopay_txn_tm >= to_date('"+ yesterdayStr +"', 'yyyyMMdd') ";
		sql += " and c.gopay_txn_tm < to_date('"+ todayStr +"', 'yyyyMMdd') ";
		sql += " group by c.main_recv_acct ";
		
		List<Object[]> recieptResults = commonQueryDAO.findBySql(sql, 0, pageSize);
		if(CollectionUtils.isEmpty(recieptResults)){
			return null;
		}
		Map<String,ReceivablesStatistic> map = new HashMap<String, ReceivablesStatistic>();
		for (Object[] objects : recieptResults) {
			ReceivablesStatistic  r = new ReceivablesStatistic();
			r.setRecieptAmt((BigDecimal)objects[0]);
			r.setAcctNo((String) objects[1]);
			String recieptCount = null == objects[2]?"0":String.valueOf(objects[2]);
			r.setRecieptCount(recieptCount);
			map.put(r.getAcctNo(), r);
		}

		return map;
		
	}

	private Map<String,ReceivablesStatistic> getTransferBackResults(String todayStr,
															  String yesterdayStr) {
		String sql = " select NVL(sum(c.mer_txn_amt),0) refund_amt,c.main_recv_acct  ,NVL(count(c.gopay_order_id),0)";
		sql += " from cps_gen_main_order c,cust_corp_info cc  ";
		sql += " where c.gopay_int_txn_cd in( '01204') ";
		sql += "  and c.txn_sta_cd = '20000' ";
		sql += "  and c.pay_cust_id = cc.cust_id  ";
		sql += "  and c.tran_finish_time >= to_date('"+ yesterdayStr +"', 'yyyyMMdd')  ";
		sql += "  and c.tran_finish_time < to_date('"+ todayStr +"', 'yyyyMMdd')  ";
		//sql += "  group by c.pay_acct ";
		sql += " group by c.main_recv_acct ";

		List<Object[]> recieptResults = commonQueryDAO.findBySql(sql, 0,
				pageSize);
		if (CollectionUtils.isEmpty(recieptResults)) {
			return null;
		}
		Map<String,ReceivablesStatistic> map = new HashMap<String, ReceivablesStatistic>();
		for (Object[] objects : recieptResults) {
			ReceivablesStatistic r = new ReceivablesStatistic();
			r.setTransferBackAmt((BigDecimal) objects[0]);
			r.setAcctNo((String) objects[1]);
			String transferBackCount = null== objects[2]?"0":String.valueOf( objects[2]);
			r.setTransferBackCount(transferBackCount);
			map.put(r.getAcctNo(), r);
		}

		return map;
	}

}
